package com.luoxue.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.luoxue.config.SqlMapper;
import com.luoxue.datasource.DynamicThreadLocalHolder;
import com.luoxue.entity.CommonValidator;
import com.luoxue.entity.DynamicSql;
import com.luoxue.entity.User;
import com.luoxue.mapper.DynamicSqlMapper;
import com.luoxue.mapper.SqlSnippetMapper;
import com.luoxue.util.ResultBody;
import org.apache.logging.log4j.util.Strings;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


@Service
public class SqlExecService {

    @Resource
    SqlMapper sqlMapper;
    @Resource
    DynamicSqlMapper dynamicSqlMapper;
    @Resource
    SqlSnippetMapper sqlSnippetMapper;

    @Resource
    ParameterValidationService parameterValidationService;

    //    @Transactional
    public Object query(Map<String, Object> param) {
        if (!param.containsKey("sql_tag")) {
            return ResultBody.error("请求参数sql_tag不存在");
        }

        String sqlTag = (String) param.get("sql_tag");
        List<DynamicSql> dynamicSqls = dynamicSqlMapper.selectBySqlTag(sqlTag);
        if (dynamicSqls == null || dynamicSqls.isEmpty()) {
            return ResultBody.error("根据请求参数sql_tag未查询到匹配的动态sql语句");
        }

        if (dynamicSqls.stream().anyMatch(dynamicSql -> Strings.isBlank(dynamicSql.getSqlResultKey()))) {
            return ResultBody.error("匹配到的动态sql语句中有字段sql_result_key为空");
        }

        List<CommonValidator> validationMsgList = parameterValidationService.parameterValidate(sqlTag, param);
        // 请求参数验证
        if (validationMsgList != null && !validationMsgList.isEmpty()) {
            return ResultBody.error(ResultBody.ERROR2_CODE, "请求参数验证失败", validationMsgList);
        }

        this.variableReplace(dynamicSqls);

        User user = (User) SecurityContextHolder.getContext().getAuthentication().getCredentials();
        param.put("user", user);

//        ExecutorService executorService = Executors.newCachedThreadPool();
        Map<String, Object> resultMap = new ConcurrentHashMap<>();

        for (DynamicSql dynamicSql : dynamicSqls) {
//            executorService.execute(() -> {
            String dataSourceName = dynamicSql.getDataSourceName();
            if (Strings.isNotBlank(dataSourceName)) {
                DynamicThreadLocalHolder.setDataSource(dataSourceName);
            }
            querySql(param, resultMap, dynamicSql);
            DynamicThreadLocalHolder.clearDataSource();
//            });
        }
//        executorService.shutdown();
//        try {
//            executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
//        } catch (InterruptedException e) {
//            e.printStackTrace();
//            return ResultBody.error(e.getMessage());
//        }
        return ResultBody.ok(resultMap);
    }

    private void variableReplace(List<DynamicSql> dynamicSqls) {
        String pattern = "\\{\\{\\s*(\\w+)\\s*}}";
        Pattern r = Pattern.compile(pattern);
        Set<String> nameSet = new HashSet<>();

        // sql语句模板变量查找
        dynamicSqls.forEach(dynamicSql -> {
            String sqlContent = dynamicSql.getSqlContent();
            Matcher m = r.matcher(sqlContent);
            while (m.find()) {
                String key = m.group(1);
                nameSet.add(key);
            }
            dynamicSql.setSqlContent(sqlContent.replaceAll(pattern, "\\{\\{$1}}"));
        });

        Map<String, String> sqlSnippetMap = new HashMap<>();

        if (!nameSet.isEmpty()) {
            // 数据库中查询sql片段列表
            sqlSnippetMapper.selectByNames(nameSet).forEach(sqlSnippet -> sqlSnippetMap.put(sqlSnippet.getName(), sqlSnippet.getContent()));
        }


        Pattern commentsP = Pattern.compile("(?ms)('(?:''|[^'])*'|\"(?:\"\"|[^\"])*\")|--.*?$|/\\*.*?\\*/");
//        String quotesContent = "'(?:''|[^'])*'|\"(?:\"\"|[^\"])*\"";
//        Pattern quotesContentP = Pattern.compile(quotesContent);
//        Pattern stripP = Pattern.compile(quotesContent + "| {2,}");

        // sql语句模板变量替换
        dynamicSqls.forEach(dynamicSql -> {
            String sqlContent = dynamicSql.getSqlContent();
            if (!sqlSnippetMap.isEmpty()) {
                Matcher m = r.matcher(sqlContent);
                sqlContent = m.replaceAll(matchResult -> {
                    String value = sqlSnippetMap.get(matchResult.group(1));
                    if (value == null) {
                        return matchResult.group(0);
                    }
                    return value;
                });
            }
            // BEGIN 去除sql注释和多余空格
            sqlContent = commentsP.matcher(sqlContent)
                    .replaceAll("$1")
                    .replaceAll("[\t\r\n][\t\r\n]?[\t\r\n]?", " ");

//            sqlContent = stripP.matcher(sqlContent).replaceAll(matchResult -> {
//                String value = matchResult.group(0);
//                if (Strings.isNotBlank(value) && quotesContentP.matcher(value).find()) {
//                    return value;
//                }
//                return " ";
//            });
            // END 去除sql注释和多余空格

            dynamicSql.setSqlContent(sqlContent);
        });
    }

    private void querySql(Map<String, Object> param, Map<String, Object> resultMap, DynamicSql dynamicSql) {
        String sqlContent = dynamicSql.getSqlContent();
        String sqlType = dynamicSql.getSqlType();

        Object sqlResult;
        try {
            sqlContent = String.format("<script>%s</script>", sqlContent);

            if (Strings.isBlank(sqlContent)) {
                sqlResult = "查询字段sql_content为空";
            } else if (Strings.isBlank(sqlType) || sqlType.equals("list")) {
                sqlResult = sqlMapper.selectList(sqlContent, param);
            } else if (sqlType.equals("page")) {
                if (param.containsKey("page_no")) {
                    int pageNo = 1;
                    int pageSize = 30;
                    try {
                        pageNo = Integer.parseInt((String) param.get("page_no"));
                        if (param.containsKey("page_size")) {
                            pageSize = Integer.parseInt((String) param.get("page_size"));
                        }
                    } catch (NumberFormatException e) {
                        e.printStackTrace();
                        resultMap.put(dynamicSql.getSqlResultKey(), e.getMessage());
                        return;
                    } finally {
                        PageHelper.startPage(pageNo, pageSize);
                        List<Map<String, Object>> list = sqlMapper.selectList(sqlContent, param);
                        //用PageInfo对结果进行包装
                        PageInfo<Map<String, Object>> page = new PageInfo<>(list);
                        Map<Object, Object> pageMap = new HashMap<>();
                        pageMap.put("total", page.getTotal());
                        pageMap.put("rows", page.getList());
                        sqlResult = pageMap;
                    }
                } else {
                    sqlResult = "请求参数page_no不存在";
                }
            } else if (sqlType.equals("object")) {
                sqlResult = sqlMapper.selectOne(sqlContent, param);
            } else if (sqlType.startsWith("@")) {
                sqlType = sqlType.substring(1);
                sqlResult = sqlMapper.selectOne(sqlContent, param).get(sqlType);
            } else if (sqlType.equals("update")) {
                sqlResult = sqlMapper.update(sqlContent, param);
            } else {
                sqlResult = "sql_type无效值";
            }
            resultMap.put(dynamicSql.getSqlResultKey(), sqlResult);
        } catch (Exception e) {
            e.printStackTrace();
            resultMap.put(dynamicSql.getSqlResultKey(), e.toString());
        }
    }
}
